package dao;

import entity.Category;
import entity.Product;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CategoryDao {
    private static PreparedStatement statement = null;
    private static ResultSet result = null;

    /**
     * 得到分类列表
     * @return 返回分类列表
     * @throws SQLException
     */
    public  static List<Category> getCategoryList() throws SQLException {
        List<Category> categoryList = new ArrayList<>();
        String sql = "SELECT * FROM category";
        statement = DataBaseOperation.getConnectionAndStatement(sql,statement);
        result = statement.executeQuery();
        while(result.next()){
            categoryList.add(new Category(result.getString("category_id"),result.getString("category_name"),
                    result.getString("note"),result.getString("default_img")));
        }
        DataBaseOperation.close(result,statement);
        return categoryList;
    }


    /**
     * 通过分类ID得到该分类下的所有商品
     * @param categoryId 分类ID
     * @return 分类下的所有商品
     * @throws SQLException
     */
    public static List<Product> getProductListByCategoryId(String categoryId) throws SQLException {
        List<Product> productList = new ArrayList<>();
        String sql = "SELECT * FROM product p INNER JOIN category c ON c.category_id = p.category_id WHERE p.category_id = ?";
        statement = DataBaseOperation.getConnectionAndStatement(sql,statement);
        statement.setInt(1,Integer.parseInt(categoryId));
        result = statement.executeQuery();
        while(result.next()){
            productList.add(new Product(result.getInt("product_id"),result.getString("product_name"),result.getInt("category_id"),
                    result.getDouble("price"),result.getString("note"),result.getString("default_img"),
                    result.getInt("hot_id"),result.getInt("review_id"),result.getInt("store")));
        }
        DataBaseOperation.close(result,statement);
        return productList;
    }


    /**
     * 通过分类ID得到分类名称
     * @param id 分类ID
     * @return 分类名称
     * @throws SQLException
     */
    public static String getCategoryName(int id) throws SQLException {
        String categoryName = "";
        String sql = "SELECT category_name FROM category WHERE category_id = " + id;
        statement = DataBaseOperation.getConnectionAndStatement(sql,statement);
        result = statement.executeQuery();
        while(result.next()){
            categoryName = result.getString("category_name");
        }
        DataBaseOperation.close(result,statement);
        return categoryName;
    }


    public static List<Product> getPageProduct(int pageIndex,int categoryId) throws SQLException {
        List<Product> productList = new ArrayList<>();
        String sql = "SELECT * FROM product p INNER JOIN category c ON p.category_id = c.category_id WHERE c.category_id = ? LIMIT ?,15";
        statement = DataBaseOperation.getConnectionAndStatement(sql,statement);
        statement.setInt(1,categoryId);
        statement.setInt(2,pageIndex * 15);
        result = statement.executeQuery();
        while(result.next()){
            productList.add(new Product(result.getInt("product_id"),result.getString("product_name"),result.getInt("category_id"),
                    result.getDouble("price"),result.getString("note"),result.getString("default_img"),
                    result.getInt("hot_id"),result.getInt("review_id"),result.getInt("store")));
        }
        DataBaseOperation.close(result,statement);
        return productList;
    }

    /**
     * 得到当前分类数据的总数居长度
     * @param categoryId 分类ID
     * @return
     * @throws SQLException
     */
    public static int getCategoryDataLength(int categoryId){
        String sql = "SELECT COUNT(*) length FROM product WHERE category_id = " + categoryId;
        int length = 0;
        try {
            statement = DataBaseOperation.getConnectionAndStatement(sql,statement);
            result = statement.executeQuery();
            while(result.next()){
                length = result.getInt("length");
            }
            DataBaseOperation.close(result,statement);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return length / 15 == 0 ? length / 15 : (length / 15) + 1;
    }
}
